<?php
class commonStatisticAction extends sfActions
{
	public function execute()
	{
	
//        $sqlString = "select month(created_datetime) as \"Thang\", year(created_datetime) as \"Nam\", count(*) as \"Tong so\"  from user GROUP //BY month(created_datetime) ORDER BY year(created_datetime) ASC, month(created_datetime) ASC
//";
//        $c = new Criteria();
//        $c->add(SqlStatisticPeer::ID, 93);
//        $this->currentSql = new SqlStatisticAdmin();
//        $this->currentSql->setSqlString($sqlString);
//		echo $this->currentSql->getSqlString($sqlString);
//        var_dump($this->currentSql->save());
//		die;

        $this->optValue = $this->getRequestParameter('optStatisticType');
		if ($this->getRequest()->getMethod() != sfRequest::POST && $this->optValue == null )
		{
			$this->sqlString = null;
			$this->permission = true;
			$c = new Criteria();
                        $c->add(SqlStatisticPeer::ID, 88,  Criteria::GREATER_EQUAL);
			$this->sqlLists = SqlStatisticPeer::doSelect($c);
			$this->result = null;
		}
		else
		{
							
			$this->actionType = $this->getRequestParameter('action_type');			
			$sqlString  = $this->getRequestParameter('sqlString');
			$this->startDate  = $this->getRequestParameter('start_date',null);
            $this->endDate  = $this->getRequestParameter('end_date',null);
			$this->page = $this->getRequestParameter('page',1);
                        $limit = 15;
			$c = new Criteria();		
			if ($this->actionType == 'update')
			{	
				$asName  = $this->getRequestParameter('asName');
				//$id  = $this->getRequestParameter('id');
				
				$c->add(SqlStatisticPeer::ID, $this->optValue);
                                $this->currentSql = SqlStatisticPeer::doSelectOne($c);
				
				if ($asName != null)
				{										
                                    $this->currentSql->setName($asName);                                    
				}
				else
				{
					$this->currentSql->setSqlString($sqlString);
				}
                                $this->currentSql->save();
			}
			else if ($this->actionType == "get" || $this->optValue != null)
			{

				$c->add(SqlStatisticPeer::ID, $this->optValue);				
				$this->currentSql = SqlStatisticPeer::doSelectOne($c);
				$sqlString = $this->currentSql->getSqlString();
                                                                
			}
			
			if ($sqlString != null)
			{
				$sqlString = trim($sqlString);
				$subSql = substr($sqlString, 0, 6);
				if ( strtolower($subSql) == "select")
				{
                                        $sqlString =substr_replace($sqlString, ' SQL_CALC_FOUND_ROWS ', 6, 0);
                                        $char_last =  substr($sqlString, -1);
                                        if($char_last == ";")
                                               $sqlString = substr ($sqlString, 0, -1);
                                        $offset = ($this->page - 1)*$limit;
                                        
                                        /*
                                         * Xu ly truong hop filter theo ngay thang cua mot so loai bao cao
                                         */ 
                                        $reports = array(89, 90, 92); //94 chưa xét
                                        					
										if (in_array($this->optValue, $reports) && ($this->startDate != null || $this->endDate != null)) {
											$dateField = '';
											
											switch ($this->optValue) {
											    case 89:
											        $dateField = 'date(rbt_approve_datetime)';
											        break;
											    case 90:
											        $dateField = 'date(created_datetime)';
											        break;
											    case 92:
											        $dateField = 'date(created_datetime)';
											        break;							        
											}
											
											$subSql = explode('group by', $sqlString);
											if (count($subSql) == 2) {											
												if (strstr($subSql[0], 'where') === false){
													$sqlString	= $subSql[0] . " where 1=1 ";
													$sqlString	.= (null != $this->startDate) ? " and " . $dateField . " >= '" . $this->startDate ."'" : "";
													$sqlString	.= (null != $this->endDate) ? " and " . $dateField . " <= '" . $this->endDate ."'" : "";
													$sqlString	.= " group by " . $subSql[1];
												} else {
													$sqlString	= $subSql[0];
													$sqlString	.= (null != $this->startDate) ? " and " . $dateField . " >= '" . $this->startDate . "'" : "";
													$sqlString	.= (null != $this->endDate) ? " and " . $dateField . " <= '" . $this->endDate . "'": "";
													$sqlString	.= " group by " . $subSql[1];
												}														
											}				
										}
										
										/*
										 * Xu ly truong hop filter theo ngay thang cua loai bao cao So thanh vien thu am online theo ngay										 
										 *  select user.id as ID, user.username as "Tên", count(*) as "Tổng số" 
										 *	from ringtone_origin inner join user on (ringtone_origin.user_id = user.id) 
										 *	where is_upload = 0 and date(ringtone_origin.created_datetime) = date(now()) - 1 
										 *	group by user.id;
										 */
										if ($this->optValue == 94 && ($this->startDate != null || $this->endDate != null)) {
											$dateField = 'date(ringtone_origin.created_datetime)';											
											$subSql = explode('group by', $sqlString);
											$select = explode('where', $subSql[0]);
											$sqlString = $select[0];
											$sqlString .= " where is_upload = 0";
											$sqlString .= (null != $this->startDate) ? " and " . $dateField . " >= '" . $this->startDate . "'" : "";
											$sqlString .= (null != $this->endDate) ? " and " . $dateField . " <= '" . $this->endDate . "'": "";
											$sqlString .= "group by" . $subSql[1];
										}
										
										if ($this->optValue == 103 && ($this->startDate != null || $this->endDate != null)) {
											$dateField = 'date(download_datetime)';
											$conditions = explode('where', $sqlString);
											
											$sqlString = $conditions[0] . " where ";
											for ($i = 1; $i < count($conditions) ; $i++) {												
												$sqlString .= (null != $this->startDate) ? $dateField . " >= '" . $this->startDate . "' and " : "";
												$sqlString .= (null != $this->endDate) ? $dateField . " <= '" . $this->endDate . "' and " : "";
												$sqlString .= $conditions[$i] . ' where ';
											}
											$sqlString = substr($sqlString, 0, -6);											
										}
                                        
                                        $sqlString .= " limit $limit offset $offset";                                                                        
					$this->result = SqlStatisticPeer::getStatistic($sqlString);                                       
					$this->permission = true;					
                                        $this->totalPage = SqlStatisticPeer::getStatistic('SELECT FOUND_ROWS() AS found_rows');
                                        foreach ($this->totalPage as $row){
                                            $totalPage = $this->totalPage->get(1);
                                        }   
                                        $this->totalPage = floor($totalPage/$limit);
                                        if($totalPage%$limit != 0)
                                            $this->totalPage += 1;
				}
				else
				{
					$this->permission = false;
				}
                                
			}
			$c = new Criteria();
                        $c->add(SqlStatisticPeer::ID, 88,  Criteria::GREATER_EQUAL);
			$this->sqlLists = SqlStatisticPeer::doSelect($c);
                        //Loc lay ten cac truong co trong cau select
                        $stringSelectStament = trim($this->currentSql->getSqlString());
                        $this->columns = split('from', substr($stringSelectStament, 6));//Loc bo chu SELECT
                        $this->columns = split(',', $this->columns[0]);//Lay ra ten cac truong select
                        $maxColumn = count($this->columns);//So luong cac truong dc lay ra
                        for ($i = 0;$i < $maxColumn;$i++ ){
                            if(strpos($this->columns[$i], ' as ') != false){//Co KEYWORD "AS"
                               $nameColumn = split('as', $this->columns[$i]);
                               $nameColumn = trim($nameColumn[1]);//Láº¥y ra tÃªn trÆ°á»�ng Ä‘Æ°á»£c gÃ¡n
                               if ($nameColumn[0] == '"'){//TÃªn trÆ°á»�ng Ä‘Æ°á»£c gÃ¡n cÃ³ dáº¡ng [...]
                                    $nameColumn = substr($nameColumn, 1,-1);//Lá»�c bá»� 2 kÃ­ tá»¥ Ä‘áº§u vÃ  cuá»‘i
                               }
                               $this->columns[$i] = trim($nameColumn);
                            }
                        }
		}
                
	}
}
?>